<?php

class SoaController extends Controller
{

	
	public function actionInternal()
	{
		
			$this->render('internal');
		
	}
	
	public function actionClient()
	{
		
			$this->render('client');
		
	}
	
	public function actionInsurance()
	{
		
			$this->render('insurance');
		
	}
	
	public function actionProduction()
	{
		
			$this->render('production');
		
	}
	
	public function actionReceipt()
	{
		
			$this->render('receipt');
		
	}
	
	public function actionVoucher()
	{
		
			$this->render('voucher');
		
	}
	
	public function actionInternalExcel()
	{
		$model = NdkHealth::model()->findAll();
		Yii::app()->request->sendFile(date('YmdHis').'.xls',
			$this->renderPartial('excelInternal', array(
				'model'=>$model
			), true)
		);
	}
	
	public function actionShowSoaReceipt()
	{
		
		$sql = " select a.id,nota_note
,(select name from mst_coa where id = a.coa_debet_id) as coa_debet
,(select name from mst_coa where id = a.coa_credit_id) as coa_kredit
,nominal
,keterangan
,createdtime as tgl_transaksi
from trx_receipt a  ";
		$command = Yii::app()->db->createCommand($sql);
		$datas = $command->queryAll();
		$jsonData='{"jsonShowInternal":[';
			foreach($datas as $data):
				$jsonData .='{"id":"'.$data['id'].'","coa_debet":"'.$data['coa_debet'].'","coa_kredit":"'.$data['coa_kredit'].'","keterangan":"'.$data['keterangan'].'","nominal":"'.$data['nominal'].'","tgl_transaksi":"'.$data['tgl_transaksi'].'","nota_note":"'.$data['nota_note'].'"},';
			endforeach;
			if(count($datas)!=0){
				$jsonData=substr($jsonData,0,strlen($jsonData)-1);
			}
			
			$jsonData .=']';
			
			$jsonData .='}';
	
			echo $jsonData;
			Yii::app()->end();
	}
	
	public function actionShowSoaVoucher()
	{
		
		$sql = " select a.id,nota_note
,(select name from mst_coa where id = a.coa_debet_id) as coa_debet
,(select name from mst_coa where id = a.coa_credit_id) as coa_kredit
,nominal
,keterangan
,createdtime as tgl_transaksi
from trx_voucher a  ";
		$command = Yii::app()->db->createCommand($sql);
		$datas = $command->queryAll();
		$jsonData='{"jsonShowInternal":[';
			foreach($datas as $data):
				$jsonData .='{"id":"'.$data['id'].'","coa_debet":"'.$data['coa_debet'].'","coa_kredit":"'.$data['coa_kredit'].'","keterangan":"'.$data['keterangan'].'","nominal":"'.$data['nominal'].'","tgl_transaksi":"'.$data['tgl_transaksi'].'","nota_note":"'.$data['nota_note'].'"},';
			endforeach;
			if(count($datas)!=0){
				$jsonData=substr($jsonData,0,strlen($jsonData)-1);
			}
			
			$jsonData .=']';
			
			$jsonData .='}';
	
			echo $jsonData;
			Yii::app()->end();
	}
	
	public function actionShowgeneralledger()
	{
		$coa = $_POST['id_coa'];
		$id_coa = MstCoa::model()->find(" code = '$coa' ")->id;
		
		$coa_exp = explode('.',$coa);
		$parent_coa = $coa_exp[0];
		$str_coa = $coa_exp[1];
		
		if($str_coa=="00"){
			$cari = " LIKE '".$parent_coa."%' ";
		}else{
			$cari = " = '".$coa."' ";
		}
		//echo $coa;exit;
		//khusus bank
		if($coa == '10A200.02' || $coa == '10A200.03' || $coa == '10A200.04' || $coa == '10A200.05' || $coa == '10A200.06' || $coa == '10A200.00'){
			$sql = " 

select case when nota_note like 'RC%' then debet - kredit
	else 0
	end as debet
	,case when nota_note like 'VC%' then kredit - debet
	else 0
	end as kredit
	,nota_note
	,keterangan 
	,tgl_transaksi
	from (
select sum(@ xx.debet) as debet,sum( @ xx.kredit) as kredit,nota_note as nota_note,'-' as keterangan,xx.tgl_transaksi from 
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan

,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code   ".$cari."
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code   ".$cari."  AND 
a.nominal > 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code   ".$cari." AND 
a.nominal > 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code   ".$cari." AND 
 a.nominal < 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code   ".$cari."  AND 
 a.nominal < 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code   ".$cari."  and a.nominal > 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code   ".$cari."  and a.nominal > 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code   ".$cari."  and a.nominal < 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan
,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code   ".$cari."  and a.nominal < 0
  )xx group by nota_note,tgl_transaksi
 )zz ";
		}else{
		
		$rumus = substr($coa,0,3);
		if($rumus == '10A' || $rumus == '10B'){
			$sql = " select 
			case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 
			else @ xx.debet
			end as debet
			,case when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.kredit
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from ";
		}else{
			$sql = " select 
			case when nota_note like 'JN%' then @ xx.debet
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.kredit 
			else @ xx.kredit
			end as debet
			,case 
			when nota_note like 'JN%' then @ xx.kredit
			when nota_note like 'RC%' OR nota_note like 'VC%' OR nota_note like 'PC%' then @ xx.debet
			else @ xx.debet
			end as kredit,xx.nota_note,xx.keterangan,xx.tgl_transaksi from ";
		}
		$sql .="
		(
select coalesce(debet,0) as debet,coalesce(credit,0) kredit,jurnal_no as nota_note,description as keterangan
,to_char(trx_date,'dd-mm-yyyy') as tgl_transaksi  
from hist_jurnal a inner join mst_coa b on a.mst_account_id = b.id where b.code ".$cari."
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a 
inner join mst_coa b on a.coa_debet_id = b.id  
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code ".$cari." AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code ".$cari." AND 
a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_receipt as nota_note,nota_note as keterangan,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code ".$cari." AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_receipt as nota_note,nota_note as keterangan,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_receipt a inner join mst_coa b on a.coa_credit_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code ".$cari." AND 
 a.nominal < 0
union
select 0 as debet,coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code ".$cari." and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet, 0 as kredit,a.no_voucher as nota_note,nota_note as keterangan,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code ".$cari." and a.nominal > 0
union
select coalesce(nominal,0) * c.konvert as debet,0 as kredit,a.no_voucher as nota_note,nota_note as keterangan,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_debet_id = b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code ".$cari." and a.nominal < 0
union
select 0 as debet, coalesce(nominal,0) * c.konvert as kredit,a.no_voucher as nota_note,nota_note as keterangan,to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi 
from trx_voucher a inner join mst_coa b on a.coa_credit_id=b.id 
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
where b.code ".$cari." and a.nominal < 0
 ";
 
 // HEALTH
 if($id_coa == '20'){ // jika dipilih piutang premi, maka select health & non health
		$sql .= " 
		union
		select 
		coalesce(a.nominal,0) * coalesce(c.nominal) debet,0 as kredit,a.nota_debet_no as nota_note
		,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2'
union
select 
		0 debet,coalesce(a.nominal,0) * coalesce(c.nominal) as kredit,a.nota_debet_no as nota_note
		,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_kredit a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2' 
union
select coalesce(nominal,0) as debet,0 as kredit, nota_no as nota_note, 'ganti_kartu' as keterangan
,to_char(createdtime,'dd-mm-yyyy') as tgl_transaksi 
from ganti_kartu where type_reg_policy = '1' ";

//NON HEALTH
		$sql .= "
		union
select
(a.premium + a.tpl + a.adjusment + a.policy_cost + a.stampduty_asuransi + a.admin_broker + a.stamp_duty -a.discount - a.diskon_asuransi) * coalesce(c.nominal) debet,0 kredit,a.nota_debet_no as nota_note
,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
from
ndk_health_non a 
inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
inner join mst_currency_detail c on b.MST_CURRENCY_DETAIL_ID = c.id
inner join MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2'
union
select 
0 debet,coalesce(a.nominal,0) * coalesce(c.nominal) as kredit,a.nota_debet_no as nota_note
,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
from ndk_health_non_kredit a 
inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
where posting = '2'
union
select coalesce(nominal,0) as debet,0 as kredit, nota_no as nota_note, 'ganti_kartu' as keterangan
,to_char(createdtime,'dd-mm-yyyy') as tgl_transaksi 
from ganti_kartu where type_reg_policy = '2' ";
}


 if($id_coa=='24'  || $id_coa == '20'){//jika dipilih HEALTH saja
	$sql .= " 
		union
		select 
		coalesce(a.nominal,0) * coalesce(c.nominal) debet,0 as kredit,a.nota_debet_no as nota_note
		,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2'
union
select 
		0 debet,coalesce(a.nominal,0) * coalesce(c.nominal) as kredit,a.nota_debet_no as nota_note
		,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_kredit a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2' 
union
select coalesce(nominal,0) as debet,0 as kredit, nota_no as nota_note, 'ganti_kartu' as keterangan
,to_char(createdtime,'dd-mm-yyyy') as tgl_transaksi 
from ganti_kartu where type_reg_policy = '1' ";
 }
 
 if($id_coa=='21' || $id_coa=='22' || $id_coa=='23' || $id_coa=='25' || $id_coa=='26'  || $id_coa == '20'){//jika dipilih NON HEALTH saja
	$sql .= "
		union
	select
	(a.premium + a.tpl + a.adjusment + a.policy_cost + a.stampduty_asuransi + a.admin_broker + a.stamp_duty -a.discount - a.diskon_asuransi) * coalesce(c.nominal) as debet,0 as kredit,a.nota_debet_no as nota_note
	,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from
	ndk_health_non a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	inner join mst_currency_detail c on b.MST_CURRENCY_DETAIL_ID = c.id
	inner join MST_CURRENCY D ON C.CURRENCY_ID = D.ID
			where posting = '2' ";
			
	if($id_coa=='21'){ //harta benda
		$sql .= " AND b.mst_jns_asuransi_id = '6' ";
	}
	
	if($id_coa=='22'){ //kendaraan bermotor
		$sql .= " AND b.mst_jns_asuransi_id = '9' ";
	}
	
	if($id_coa=='23'){ //kecelakaan diri
		$sql .= " AND b.mst_jns_asuransi_id = '11' ";
	}
	
	if($id_coa=='25'){ //jiwa
		$sql .= " AND b.mst_jns_asuransi_id = '12' ";
	}
	
	if($id_coa=='26'){ //aneka
		$sql .= " AND b.mst_jns_asuransi_id = '18' ";
	}
			
	$sql.= "
	union
	select 
	0 debet,coalesce(a.nominal,0) * coalesce(c.nominal) as kredit,a.nota_debet_no as nota_note
	,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non_kredit a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2' ";
	
	if($id_coa=='21'){ //harta benda
		$sql .= " AND b.mst_jns_asuransi_id = '6' ";
	}
	
	if($id_coa=='22'){ //kendaraan bermotor
		$sql .= " AND b.mst_jns_asuransi_id = '9' ";
	}
	
	if($id_coa=='23'){ //kecelakaan diri
		$sql .= " AND b.mst_jns_asuransi_id = '11' ";
	}
	
	if($id_coa=='25'){ //jiwa
		$sql .= " AND b.mst_jns_asuransi_id = '12' ";
	}
	
	if($id_coa=='26'){ //aneka
		$sql .= " AND b.mst_jns_asuransi_id = '18' ";
	}
	
	$sql.= "
	union
	select coalesce(a.nominal,0) as debet,0 as kredit, a.nota_no as nota_note, 'ganti_kartu' as keterangan
	,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi 
	from ganti_kartu a 
	inner join reg_health_policy_non b on a.reg_policy_id = b.id
	where a.type_reg_policy = '2' ";
	
	if($id_coa=='21'){ //harta benda
		$sql .= " AND b.mst_jns_asuransi_id = '6' ";
	}
	
	if($id_coa=='22'){ //kendaraan bermotor
		$sql .= " AND b.mst_jns_asuransi_id = '9' ";
	}
	
	if($id_coa=='23'){ //kecelakaan diri
		$sql .= " AND b.mst_jns_asuransi_id = '11' ";
	}
	
	if($id_coa=='25'){ //jiwa
		$sql .= " AND b.mst_jns_asuransi_id = '12' ";
	}
	
	if($id_coa=='26'){ //aneka
		$sql .= " AND b.mst_jns_asuransi_id = '18' ";
	}
 }

 
 
 if($id_coa == '61'  || $id_coa == '59'){ // jika dipilih piutang pajak pph 23
		$sql .= " 
		union
		select 
		coalesce(a.pph,0) * coalesce(c.nominal) as debet,0 kredit,'PPh-'||a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2'
union
select 
		coalesce(a.pph,0) * coalesce(c.nominal) as debet,0 kredit,'PPh-'||a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_non a 
		inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2' 
union
select 
		0 as debet,coalesce(a.pph,0) * coalesce(c.nominal) kredit,'PPh-'||a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_kredit a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2' 
union
select 
	0 as debet,coalesce(a.pph,0) * coalesce(c.nominal) kredit,'PPh-'||a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non_kredit a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2'	
 ";

}

 if($id_coa == '105'  || $id_coa == '102'){ // jika dipilih hutang pajak ppn
		$sql .= " union
select 
		coalesce(a.ppn,0) * coalesce(c.nominal)  as debet,0 as kredit,'PPn-'||a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2'
union
select 
		coalesce(a.ppn,0) * coalesce(c.nominal) as debet,0 kredit,'PPn-'||a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_non a 
		inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2' 
union
select 
		0 as debet,coalesce(a.ppn,0) * coalesce(c.nominal) as kredit,'PPn-'||a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_kredit a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2' 
union
select 
	0 as debet,coalesce(a.ppn,0) * coalesce(c.nominal) kredit,'PPn-'||a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non_kredit a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2'		



	
		";
}

 if($id_coa == '97'  || $id_coa == '93'){ // jika dipilih hutang premi health
$sql .= " 
union
select 
		 coalesce(a.nett_to_insurance,0) * coalesce(c.nominal) as debet,0 as kredit,a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2'
union
select 
		0 debet,coalesce(a.nett_to_insurance,0) * coalesce(c.nominal) as kredit,a.nota_debet_no as nota_note
		,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_kredit a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2' 
";
}//end hutang premi health

 if($id_coa == '94' || $id_coa == '95' || $id_coa == '96' || $id_coa == '98' || $id_coa == '99'  || $id_coa == '93'){ // jika dipilih hutang premi non health
 $sql .= "
union
select 
		((a.premium + a.tpl - a.diskon_asuransi + a.policy_cost + a.stampduty_asuransi - a.brokerage - a.ppn + a.pph) * coalesce(c.nominal) ) + (coalesce(a.tpl,0) * coalesce(c.nominal) )
			as debet
		,0  as kredit,a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_non a 
		inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2' ";
		if($id_coa=='94'){ //harta benda
		$sql .= " AND b.mst_jns_asuransi_id = '6' ";
	}
	
	if($id_coa=='95'){ //kendaraan bermotor
		$sql .= " AND b.mst_jns_asuransi_id = '9' ";
	}
	
	if($id_coa=='96'){ //kecelakaan diri
		$sql .= " AND b.mst_jns_asuransi_id = '11' ";
	}
	
	if($id_coa=='98'){ //jiwa
		$sql .= " AND b.mst_jns_asuransi_id = '12' ";
	}
	
	if($id_coa=='99'){ //aneka
		$sql .= " AND b.mst_jns_asuransi_id = '18' ";
	}
	
	 $sql .= "
union
select 
	0 debet,coalesce(a.nett_to_insurance,0) * coalesce(c.nominal) as kredit,a.nota_debet_no as nota_note
	,a.note as keterangan,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non_kredit a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2'  ";
		if($id_coa=='94'){ //harta benda
		$sql .= " AND b.mst_jns_asuransi_id = '6' ";
	}
	
	if($id_coa=='95'){ //kendaraan bermotor
		$sql .= " AND b.mst_jns_asuransi_id = '9' ";
	}
	
	if($id_coa=='96'){ //kecelakaan diri
		$sql .= " AND b.mst_jns_asuransi_id = '11' ";
	}
	
	if($id_coa=='98'){ //jiwa
		$sql .= " AND b.mst_jns_asuransi_id = '12' ";
	}
	
	if($id_coa=='99'){ //aneka
		$sql .= " AND b.mst_jns_asuransi_id = '18' ";
	}
	
	 $sql .= "
union
	select 0 as debet,coalesce(a.nominal,0) as kredit, a.nota_no as nota_note, 'ganti_kartu' as keterangan
	,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi 
	from ganti_kartu a 
 ";
}

 if($id_coa == '130'  || $id_coa == '126'){ // jika dipilih R/L - pendapatan jasa keperantaraan health
$sql .= " 
union
select 
	coalesce(brokerage,0) * c.nominal - coalesce(discount,0) * c.nominal + coalesce(adjusment,0) * c.nominal + 
	coalesce(diskon_asuransi,0) * c.nominal as debet,0 kredit,a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health a 
	inner join reg_health_policy b on a.reg_health_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2'
union
select 
		0 as debet,coalesce(brokerage,0) * c.nominal - coalesce(discount,0) * c.nominal + coalesce(adjusment,0) * c.nominal + 
	coalesce(diskon_asuransi,0) * c.nominal kredit,a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_kredit a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2'
 ";
}
if($id_coa == '127' || $id_coa == '128' || $id_coa == '129' || $id_coa == '131' || $id_coa == '132' || $id_coa == '126'){ // jika dipilih R/L - pendapatan jasa keperantaraan health
$sql .=" 
union
select 
	coalesce(brokerage,0) * c.nominal - coalesce(discount,0) * c.nominal + coalesce(adjusment,0) * c.nominal + 
	coalesce(diskon_asuransi,0) * c.nominal as debet,0 as kredit,a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID where posting = '2'
  ";
		if($id_coa=='127'){ //harta benda
		$sql .= " AND b.mst_jns_asuransi_id = '6' ";
	}
	
	if($id_coa=='128'){ //kendaraan bermotor
		$sql .= " AND b.mst_jns_asuransi_id = '9' ";
	}
	
	if($id_coa=='129'){ //kecelakaan diri
		$sql .= " AND b.mst_jns_asuransi_id = '11' ";
	}
	
	if($id_coa=='131'){ //jiwa
		$sql .= " AND b.mst_jns_asuransi_id = '12' ";
	}
	
	if($id_coa=='132'){ //aneka
		$sql .= " AND b.mst_jns_asuransi_id = '18' ";
	}
	$sql .="
union
select 
	0 as debet,coalesce(brokerage,0) * c.nominal - coalesce(discount,0) * c.nominal + coalesce(adjusment,0) * c.nominal + 
	coalesce(diskon_asuransi,0) * c.nominal as kredit,a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non_kredit a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2'
	 ";
	if($id_coa=='127'){ //harta benda
		$sql .= " AND b.mst_jns_asuransi_id = '6' ";
	}
	
	if($id_coa=='128'){ //kendaraan bermotor
		$sql .= " AND b.mst_jns_asuransi_id = '9' ";
	}
	
	if($id_coa=='129'){ //kecelakaan diri
		$sql .= " AND b.mst_jns_asuransi_id = '11' ";
	}
	
	if($id_coa=='131'){ //jiwa
		$sql .= " AND b.mst_jns_asuransi_id = '12' ";
	}
	
	if($id_coa=='132'){ //aneka
		$sql .= " AND b.mst_jns_asuransi_id = '18' ";
	}
	
}

 if($id_coa == '198' || $id_coa == '194'){ // jika dipilih R/L - hasil lainnya - Lainnya
$sql .= " 
union
select 
	coalesce(admin_broker,0) * c.nominal + coalesce(stamp_duty,0) * c.nominal as debet,0 kredit,a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health a 
	inner join reg_health_policy b on a.reg_health_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2'
union
select 
	coalesce(admin_broker,0) * c.nominal + coalesce(stamp_duty,0) * c.nominal as debet,0 kredit,a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2'
union
select 
		0 as debet,coalesce(admin_broker,0) * c.nominal + coalesce(stamp_duty,0) * c.nominal kredit,a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
		from ndk_health_kredit a 
		inner join reg_health_policy b on a.reg_health_policy_id = b.id
		INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
		INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
		where posting = '2' 
union
select 
	0 as debet,coalesce(admin_broker,0) * c.nominal + coalesce(stamp_duty,0) * c.nominal kredit,a.nota_debet_no as nota_note,a.note as keterangan
		,to_char(a.createdtime,'dd-mm-yyyy') as tgl_transaksi
	from ndk_health_non_kredit a 
	inner join reg_health_policy_non b on a.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL C ON B.MST_CURRENCY_DETAIL_ID  = C.ID
	INNER JOIN MST_CURRENCY D ON C.CURRENCY_ID = D.ID
	where posting = '2'	
	";
}

$sql .= " )xx order by nota_note	 ";
}
//echo $sql;exit; //nanaganteng
		$command = Yii::app()->db->createCommand($sql);
		
		$datas = $command->queryAll();
		
		if(isset($_POST['yt0'])){
			$cmd_listcoa = Yii::app()->db->createCommand(" SELECT a.id,a.code,
(select b.name from mst_coa b where b.code=split_part(a.code, '.', 1)||'.00')||'--'||a.name as name 
FROM MST_COA a WHERE a.CODE NOT LIKE '%00.00' AND a.NAME != '-' ORDER BY CODE ");
			$list_coa = $cmd_listcoa->queryAll();
			
			$session=new CHttpSession;
            $session->open();
		Yii::import('application.extensions.giiplus.bootstrap.*');
		require_once('tcpdf/tcpdf.php');
		require_once('tcpdf/config/lang/eng.php');
		$html = $this->renderPartial('/finance/cetakGL', array(
				'list_coa'=>$list_coa,
				'month'=>$this->bulan_convert($_POST['bulan']),
			), true);
		
		//die($html);
		
		$pdf = new TCPDF();
			$pdf->SetCreator(PDF_CREATOR);
			$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
			$pdf->setPrintHeader(false);
			$pdf->setPrintFooter(false);
			$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);
			$pdf->SetFont('dejavusans', '', 10);
			$pdf->AddPage();
			$pdf->writeHTML($html, true, true, true, true, '');
			$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
			$pdf->LastPage();
		$pdf->Output("report_GL.pdf", "I");
		}else{
			echo json_encode($datas);
			Yii::app()->end();
		}
	}
	
	public function bulan_convert($id){
		$bulan= array("01"=>"Januari","02"=>"Februari","03"=>"Maret","04"=>"April","05"=>"Mei","06"=>"Juni","07"=>"Juli","08"=>"Agustus","09"=>"Sempember","10"=>"Oktober","11"=>"November","12"=>"Desember");
		return $bulan[$id];
	}
	
	
	public function actionShowRenewal()
	{
		$sql = " 
	
	select a.name,'HEALTH' as jns_insurance,E.code as jns_matauang,sum(c.premium) as gross_premi,sum(brokerage) as brokerage,
	b.policy_no,to_char(b.start_periode,'dd-mm-yyyy')||' s/d '||to_char(b.end_periode,'dd-mm-yyyy') periode
	from mst_client a 
	inner join reg_health_policy b on a.id = b.mst_client_id
	inner join ndk_health c on c.reg_health_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL D ON B.MST_CURRENCY_DETAIL_ID  = D.ID
	INNER JOIN MST_CURRENCY E ON D.CURRENCY_ID = E.ID
	group by a.name,e.code,b.policy_no,to_char(b.start_periode,'dd-mm-yyyy'),to_char(b.end_periode,'dd-mm-yyyy')
union 
	select a.name,'NON HEALTH' as jns_insurance,E.code as jns_matauang,coalesce(sum(c.premium+c.tpl),0) as gross_premi
	,coalesce(sum(brokerage),0) as brokerage,b.policy_no
	,to_char(b.start_periode,'dd-mm-yyyy')||' s/d '||to_char(b.end_periode,'dd-mm-yyyy') periode
	from mst_client a 
	inner join reg_health_policy_non b on a.id = b.mst_client_id
	inner join ndk_health_non c on c.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL D ON B.MST_CURRENCY_DETAIL_ID  = D.ID
	INNER JOIN MST_CURRENCY E ON D.CURRENCY_ID = E.ID
	group by a.name,e.code,b.policy_no,to_char(b.start_periode,'dd-mm-yyyy'),to_char(b.end_periode,'dd-mm-yyyy')
	order by periode 
  ";

	$command = Yii::app()->db->createCommand($sql);
		
		$datas = $command->queryAll();
		
		if(isset($_POST['yt0'])){
			$session=new CHttpSession;
            $session->open();
		Yii::import('application.extensions.giiplus.bootstrap.*');
		require_once('tcpdf/tcpdf.php');
		require_once('tcpdf/config/lang/eng.php');
		$html = $this->renderPartial('/finance/excelReportRenewal', array(
				'month'=>$month,				
				'bulan'=>$bulan,
				'tahun'=>$tahun,
				'data'=>$datas,
			), true);
		
		//die($html);
		
		$pdf = new TCPDF();
			$pdf->SetCreator(PDF_CREATOR);
			$pdf->SetAutoPageBreak(TRUE, 0);
			$pdf->setPrintHeader(false);
			$pdf->setPrintFooter(false);
			$pdf->SetAutoPageBreak(TRUE, 0);
			$pdf->SetFont('dejavusans', '', 10);
			$pdf->AddPage('L', 'A4');
			$pdf->writeHTML($html, true, true, true, true, '');
			$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
			$pdf->LastPage();
		$pdf->Output("report_renewal.pdf", "I");
		}else{
			echo json_encode($datas);
			Yii::app()->end();
		}
			
	}
	
	public function actionShowAging()
	{
		$sql = " 
	
	select a.client_number ||' /Health' as client_number,a.name ||'( '||c.nota_debet_no||') ' as name,'HEALTH' as jns_insurance,E.code as jns_mata_uang,sum(c.premium) as gross_premi
	,case when date_part('day',now()-c.due_date) <=0 then 'Y'
	else ''
	end telat1
	,case when date_part('day',now()-c.due_date) >0 and date_part('day',now()-c.due_date) <= 30 then 'Y'
	else ''
	end telat2
	,case when date_part('day',now()-c.due_date) >30 and date_part('day',now()-c.due_date) <= 60 then 'Y'
	else ''
	end telat3
	,case when date_part('day',now()-c.due_date) >60 and date_part('day',now()-c.due_date) <= 90 then 'Y'
	else ''
	end telat4
	,case when date_part('day',now()-c.due_date) >90  then 'Y'
	else ''
	end telat5,c.nota_debet_no
	from mst_client a 
	inner join reg_health_policy b on a.id = b.mst_client_id
	inner join ndk_health c on c.reg_health_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL D ON B.MST_CURRENCY_DETAIL_ID  = D.ID
	INNER JOIN MST_CURRENCY E ON D.CURRENCY_ID = E.ID
	group by a.name,e.code,a.client_number,c.due_date,c.nota_debet_no
union 
	select a.client_number||' /Non Health' as client_number,a.name ||'( '||c.nota_debet_no||') ' as name,'NON HEALTH' as jns_insurance,E.code as jns_mata_uang,coalesce(sum(c.premium+c.tpl),0) as gross_premi
	,case when date_part('day',now()-c.due_date) <=0 then 'Y'
	else ''
	end telat1
		,case when date_part('day',now()-c.due_date) >0 and date_part('day',now()-c.due_date) <= 30 then 'Y'
	else ''
	end telat2
	,case when date_part('day',now()-c.due_date) >30 and date_part('day',now()-c.due_date) <= 60 then 'Y'
	else ''
	end telat3
	,case when date_part('day',now()-c.due_date) >60 and date_part('day',now()-c.due_date) <= 90 then 'Y'
	else ''
	end telat4
	,case when date_part('day',now()-c.due_date) >90  then 'Y'
	else ''
	end telat5,c.nota_debet_no
	from mst_client a 
	inner join reg_health_policy_non b on a.id = b.mst_client_id
	inner join ndk_health_non c on c.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL D ON B.MST_CURRENCY_DETAIL_ID  = D.ID
	INNER JOIN MST_CURRENCY E ON D.CURRENCY_ID = E.ID
	group by a.name,e.code,a.client_number,c.due_date,c.nota_debet_no
order by jns_insurance,nota_debet_no
  ";

	$command = Yii::app()->db->createCommand($sql);
	$datas = $command->queryAll();
	
		if(isset($_POST['yt0'])){
			$session=new CHttpSession;
            $session->open();
		Yii::import('application.extensions.giiplus.bootstrap.*');
		require_once('tcpdf/tcpdf.php');
		require_once('tcpdf/config/lang/eng.php');
		$html = $this->renderPartial('/finance/excelReportAging', array(
				'month'=>$month,
				'tanggal'=>$tanggal,
				'bulan'=>$bulan,
				'tahun'=>$tahun,
				'data'=>$datas,
			), true);
		
		//die($html);
		
		$pdf = new TCPDF();
			$pdf->SetCreator(PDF_CREATOR);
			$pdf->SetAutoPageBreak(TRUE, 0);
			$pdf->setPrintHeader(false);
			$pdf->setPrintFooter(false);
			$pdf->SetAutoPageBreak(TRUE, 0);
			$pdf->SetFont('dejavusans', '', 10);
			$pdf->AddPage('L', 'A4');
			$pdf->writeHTML($html, true, true, true, true, '');
			$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
			$pdf->LastPage();
			$pdf->Output("report_aging.pdf", "I");
		}else{
			echo json_encode($datas);
				Yii::app()->end();
		}		
	}
	
	public function actionShowBank()
	{
		$cb_coa = $_POST['cb_coa'];
		$sql = " select tgl_transaksi,nota_note,debet,jns_mata_uang,sum(credit) as credit,client_name ,coa_credit_id from (
select to_char(tgl_transaksi,'dd-mm-yyyy') as tgl_transaksi
		,no_voucher as nota_note
		,0 as debet,
case when a.jns_mata_uang ='1' then 'Rp'
     when a.jns_mata_uang = '2' then 'USD'
     else 'EUR' end as jns_mata_uang,coalesce(nominal,0) * c.konvert as credit ,client_name, coa_credit_id
from trx_voucher  a
inner join mst_coa b on a.coa_debet_id = b.id
inner join (
	select b.nominal as konvert
	,case when b.code = 'IDR' then '1'
	when b.code = 'USD' then '2'
	else '3'
	end as id_uang
	 from (
	select max(id) id from mst_currency_detail group by currency_id
	)a left join mst_currency_detail b on a.id = b.id
)c on a.jns_mata_uang = c.id_uang
)x
where coa_credit_id='".$cb_coa."' 
group by tgl_transaksi,nota_note,debet,jns_mata_uang,client_name,coa_credit_id
order by nota_note ";
		$command = Yii::app()->db->createCommand($sql);
		
		
		
		$datas = $command->queryAll();
		
		if(isset($_POST['yt0'])){
			$session=new CHttpSession;
            $session->open();
		Yii::import('application.extensions.giiplus.bootstrap.*');
		require_once('tcpdf/tcpdf.php');
		require_once('tcpdf/config/lang/eng.php');
              
		
		$html = $this->renderPartial('/Finance/excelReportBank', array(
				'month'=>$month,
				'tahun'=>$tahun,
				'data'=>$datas,
				'bank'=>$cb_coa,
			), true);
		
		//die($html);
		
		$pdf = new TCPDF();
			$pdf->SetCreator(PDF_CREATOR);
			$pdf->SetAutoPageBreak(TRUE, 0);
			$pdf->setPrintHeader(false);
			$pdf->setPrintFooter(false);
			$pdf->SetAutoPageBreak(TRUE, 0);
			$pdf->SetFont('dejavusans', '', 10);
			$pdf->AddPage();
			$pdf->writeHTML($html, true, true, true, true, '');
			$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
			$pdf->LastPage();
		$pdf->Output("report_bank.pdf", "I");
		}else{
		echo json_encode($datas);
			Yii::app()->end();
			}
	}
	
	
	public function actionShowSoaProduction()
	{
		
		$sql = " select a.name as nama_client,'HEALTH' as jns_insurance,E.name as currency,sum(c.premium) as nominal,sum(brokerage) as brokerage,
	b.policy_no,to_char(b.start_periode,'dd-mm-yyyy') as start_periode,to_char(b.end_periode,'dd-mm-yyyy') as end_periode
	,c.type
	from mst_client a 
	inner join reg_health_policy b on a.id = b.mst_client_id
	inner join ndk_health c on c.reg_health_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL D ON B.MST_CURRENCY_DETAIL_ID  = D.ID
	INNER JOIN MST_CURRENCY E ON D.CURRENCY_ID = E.ID
	group by a.name,e.name,b.policy_no,to_char(b.start_periode,'dd-mm-yyyy'),to_char(b.end_periode,'dd-mm-yyyy'),c.type
	union 
	select a.name as nama_client,'NOTA KREDIT',E.name as currency,cast('-'||c.nominal as double precision) as nominal,brokerage as brokerage,
	b.policy_no,to_char(b.start_periode,'dd-mm-yyyy') as start_periode,to_char(b.end_periode,'dd-mm-yyyy') as end_periode
	,c.type
	from mst_client a 
	inner join reg_health_policy b on a.id = b.mst_client_id
	inner join ndk_health_kredit c on c.reg_health_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL D ON B.MST_CURRENCY_DETAIL_ID  = D.ID
	INNER JOIN MST_CURRENCY E ON D.CURRENCY_ID = E.ID
	
  ";
  
  $sql2 = " 
  select a.name as nama_client,F.NAME as jns_insurance,E.name as currency
,coalesce(sum(c.premium+c.tpl),0) as nominal
,case when c.client_tahu_diskon = '1' then coalesce(sum(brokerage),0) + coalesce(sum(adjusment),0) 
- coalesce(sum(discount),0) + coalesce(sum(diskon_asuransi),0)
else 
coalesce(sum(brokerage),0) + coalesce(sum(adjusment),0) 
- coalesce(sum(discount),0)
end as brokerage
,b.policy_no
	,to_char(b.start_periode,'dd-mm-yyyy') as start_periode,to_char(b.end_periode,'dd-mm-yyyy') as end_periode,c.type
	from mst_client a 
	inner join reg_health_policy_non b on a.id = b.mst_client_id
	inner join ndk_health_non c on c.reg_nonhealth_policy_id = b.id
	INNER JOIN MST_CURRENCY_DETAIL D ON B.MST_CURRENCY_DETAIL_ID  = D.ID
	INNER JOIN MST_CURRENCY E ON D.CURRENCY_ID = E.ID
	INNER JOIN MST_JNS_ASURANSI F ON b.MST_JNS_ASURANSI_ID = F.ID
	group by c.client_tahu_diskon,a.name,e.name,F.NAME,b.policy_no,to_char(b.start_periode,'dd-mm-yyyy'),to_char(b.end_periode,'dd-mm-yyyy'),c.type
 ";
 
		$command = Yii::app()->db->createCommand($sql);
		$datas = $command->queryAll();
		
		$command2 = Yii::app()->db->createCommand($sql2);
		$datas2 = $command2->queryAll();
		
		$jsonData='{"jsonShowInternal":[';
			foreach($datas as $data):
				$jsonData .='{"nama_client":"'.$data['nama_client'].'","start_periode":"'.$data['start_periode'].'","end_periode":"'.$data['end_periode'].'","currency":"'.$data['currency'].'","nominal":"'.$data['nominal'].'","brokerage":"'.$data['brokerage'].'","type":"'.$data['type'].'"},';
			endforeach;
			if(count($datas)!=0){
				$jsonData=substr($jsonData,0,strlen($jsonData)-1);
			}
			$jsonData .=']';
			
			$jsonData .=',"jsonShowInternal2":[';
			foreach($datas2 as $data):
				$jsonData .='{"nama_client":"'.$data['nama_client'].'","start_periode":"'.$data['start_periode'].'","end_periode":"'.$data['end_periode'].'","currency":"'.$data['currency'].'","nominal":"'.$data['nominal'].'","brokerage":"'.$data['brokerage'].'","type":"'.$data['type'].'","jns_insurance":"'.$data['jns_insurance'].'"},';
			endforeach;
			if(count($datas2)!=0){
				$jsonData =substr($jsonData,0,strlen($jsonData)-1);
			}
			$jsonData .=']';
			
			
			$jsonData .='}';
			
		

	
			echo $jsonData;
			Yii::app()->end();
	}
	
	public function actionShowSoaInternal()
	{
		
		$sql = " select z.*,case when nominal is null then 'O/S'
      when jml_bayar_konv >= nominal_konv then 'Marry On Gross'
      when nominal_konv - jml_bayar_konv <= 3000 then 'Force Marry'
else
'O/S'
end as status
,nominal_konv - jml_bayar_konv as kurang_bayar
 from (
select 
b.policy_no
,c.id
,'H' as status_nota
,c.nota_debet_no
,'health' as type
,d.name
,c.nominal
,c.nominal * h.nominal as nominal_konv
,c.discount
,c.policy_cost
,c.stamp_duty
,c.admin_broker
,c.adjusment
,c.brokerage
,c.nett_to_insurance
,c.nett_to_insurance * h.nominal as nett_to_insurance_konv
,case 
      when e.nominal is not null then e.nominal 
      when e.nominal is not null and e.jns_mata_uang != '1' then e.nominal / h.nominal
      else 0 end as jml_bayar
,case 
      when e.nominal is not null then e.nominal 
      when e.nominal is not null and e.jns_mata_uang != '1' then e.nominal * h.nominal
      else 0 end as jml_bayar_konv
,a.name as nama_client
,f.code
,i.no_type
,c.diskon_asuransi
,case when e.jns_mata_uang ='1' then 'IDR'
	when e.jns_mata_uang ='2' then 'USD'
	else 'EUR'
end as mata_uang_bayar
,case 
	 when e.nominal is not null and e.jns_mata_uang = '1' then e.nominal / h.nominal
      when e.nominal is not null then e.nominal      
      else 0 end as jml_bayar_sesuai_matauang
from 
mst_client a
inner join reg_health_policy b on a.id = b.mst_client_id
inner join ndk_health c on c.REG_HEALTH_POLICY_ID = b.ID
left join mst_perusahaan_asuransi d on b.mst_perusahaan_asuransi_id = d.id
left join  (select nota_note,jns_mata_uang,sum(nominal) nominal from trx_receipt group by nota_note,jns_mata_uang )  e on c.nota_debet_no = e.nota_note
left join mst_currency f on b.mst_currency_id = f.id
left join mst_currency_detail h on b.mst_currency_detail_id = h.id
left join mst_jns_asuransi i on b.mst_jns_asuransi_id = i.id
UNION
select 
b.policy_no
,c.id
,'NH' as status_nota
,c.nota_debet_no
,g.name as type
,d.name
,(c.premium + c.tpl + c.adjusment + c.policy_cost + c.stampduty_asuransi + c.admin_broker + c.stamp_duty -c.discount - c.diskon_asuransi) nominal
,((c.premium + c.tpl + c.adjusment + c.policy_cost + c.stampduty_asuransi + c.admin_broker + c.stamp_duty -c.discount - c.diskon_asuransi)) * h.nominal as nominal_konv
,c.discount
,c.policy_cost
,c.stamp_duty
,c.admin_broker
,c.adjusment
,c.brokerage
,(c.premium + c.tpl - c.diskon_asuransi + c.policy_cost + c.stampduty_asuransi - c.brokerage - c.ppn + c.pph) as nett_to_insurance
,(c.premium + c.tpl - c.diskon_asuransi + c.policy_cost + c.stampduty_asuransi - c.brokerage - c.ppn + c.pph) * h.nominal as nett_to_insurance_konv
,case 
      when e.nominal is not null then e.nominal 
      when e.nominal is not null and e.jns_mata_uang != '1' then e.nominal / h.nominal
      else 0 end as jml_bayar
,case 
      when e.nominal is not null then e.nominal 
      when e.nominal is not null and e.jns_mata_uang != '1' then e.nominal * h.nominal
      else 0 end as jml_bayar_konv
,a.name as nama_client
,f.code
,i.no_type
,c.diskon_asuransi
,case when e.jns_mata_uang ='1' then 'IDR'
	when e.jns_mata_uang ='2' then 'USD'
	else 'EUR'
end as mata_uang_bayar
,case 
	 when e.nominal is not null and e.jns_mata_uang = '1' then e.nominal / h.nominal
      when e.nominal is not null then e.nominal      
      else 0 end as jml_bayar_sesuai_matauang
from 
mst_client a
inner join reg_health_policy_non b on a.id = b.mst_client_id
inner join ndk_health_non c on c.REG_NONHEALTH_POLICY_ID = b.ID
left join mst_perusahaan_asuransi d on b.mst_perusahaan_asuransi_id = d.id
left join  (select nota_note,jns_mata_uang,sum(nominal) nominal from trx_receipt group by nota_note,jns_mata_uang )  e on c.nota_debet_no = e.nota_note
left join mst_currency f on b.mst_currency_id = f.id
left join mst_currency_detail h on b.mst_currency_detail_id = h.id
inner join mst_jns_asuransi g on b.mst_jns_asuransi_id = g.id
left join mst_jns_asuransi i on b.mst_jns_asuransi_id = i.id
)z 
 "; // where jml_bayar_konv < nominal_konv
		$command = Yii::app()->db->createCommand($sql);
		$datas = $command->queryAll();
		
		if(isset($_POST['yt0'])){
			$session=new CHttpSession;
            $session->open();
		Yii::import('application.extensions.giiplus.bootstrap.*');
		require_once('tcpdf/tcpdf.php');
		require_once('tcpdf/config/lang/eng.php');
		$html = $this->renderPartial('excelSoatoinsurance', array(
					'datas'=>$datas,
				), true);
		
		//die($html);
		
		$pdf = new TCPDF();
			$pdf->SetCreator(PDF_CREATOR);
			$pdf->SetAutoPageBreak(TRUE, 0);
			$pdf->setPrintHeader(false);
			$pdf->setPrintFooter(false);
			$pdf->SetAutoPageBreak(TRUE, 0);
			$pdf->SetFont('dejavusans', '', 10);
			$pdf->AddPage('L', 'A4');
			$pdf->writeHTML($html, true, true, true, true, '');
			$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
			$pdf->LastPage();
		$pdf->Output("report.pdf", "I");
		}else if(isset($_POST['yt1'])){
			$session=new CHttpSession;
            $session->open();
		Yii::import('application.extensions.giiplus.bootstrap.*');
		require_once('tcpdf/tcpdf.php');
		require_once('tcpdf/config/lang/eng.php');
		$html = $this->renderPartial('excelSoatoclient', array(
					'datas'=>$datas,
				), true);
		
		//die($html);
		
		$pdf = new TCPDF();
			$pdf->SetCreator(PDF_CREATOR);
			$pdf->SetAutoPageBreak(TRUE, 0);
			$pdf->setPrintHeader(false);
			$pdf->setPrintFooter(false);
			$pdf->SetAutoPageBreak(TRUE, 0);
			$pdf->SetFont('dejavusans', '', 10);
			$pdf->AddPage('L', 'A4');
			$pdf->writeHTML($html, true, true, true, true, '');
			$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
			$pdf->LastPage();
		$pdf->Output("report.pdf", "I");
		}else if(isset($_POST['yt2'])){
			$session=new CHttpSession;
            $session->open();
		Yii::import('application.extensions.giiplus.bootstrap.*');
		require_once('tcpdf/tcpdf.php');
		require_once('tcpdf/config/lang/eng.php');
		$html = $this->renderPartial('excelSoatointernal', array(
					'datas'=>$datas,
				), true);
		
		//die($html);
		
		$pdf = new TCPDF();
			$pdf->SetCreator(PDF_CREATOR);
			$pdf->SetAutoPageBreak(TRUE, 0);
			$pdf->setPrintHeader(false);
			$pdf->setPrintFooter(false);
			$pdf->SetAutoPageBreak(TRUE, 0);
			$pdf->SetFont('dejavusans', '', 10);
			$pdf->AddPage('L', 'A4');
			$pdf->writeHTML($html, true, true, true, true, '');
			$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
			$pdf->LastPage();
		$pdf->Output("report.pdf", "I");
		}else{
			$jsonData='{"jsonShowInternal":[';
			foreach($datas as $data):
				$jsonData .='{"policy_no":"'.$data['policy_no'].'","nota_debet_no":"'.$data['nota_debet_no'].'","type":"'.$data['type'].'","name":"'.$data['name'].'","nominal":"'.$data['nominal'].'","discount":"'.$data['discount'].'","policy_cost":"'.$data['policy_cost'].'","stamp_duty":"'.$data['stamp_duty'].'","admin_broker":"'.$data['admin_broker'].'","adjusment":"'.$data['adjusment'].'","brokerage":"'.$data['brokerage'].'","nett_to_insurance":"'.$data['nett_to_insurance'].'","jml_bayar":"'.$data['jml_bayar'].'","nama_client":"'.$data['nama_client'].'","status":"'.$data['status'].'","code":"'.$data['code'].'","id":"'.$data['id'].'","status_nota":"'.$data['status_nota'].'","nominal_konv":"'.$data['nominal_konv'].'","nett_to_insurance_konv":"'.$data['nett_to_insurance_konv'].'","jml_bayar_konv":"'.$data['jml_bayar_konv'].'","kurang_bayar":"'.$data['kurang_bayar'].'","no_type":"'.$data['no_type'].'","mata_uang_bayar":"'.$data['mata_uang_bayar'].'","diskon_asuransi":"'.$data['diskon_asuransi'].'","jml_bayar_sesuai_matauang":"'.$data['jml_bayar_sesuai_matauang'].'"},';
			endforeach;
			if(count($datas)!=0){
				$jsonData=substr($jsonData,0,strlen($jsonData)-1);
			}
			
			$jsonData .=']';
			
			$jsonData .='}';
	
			echo $jsonData;
			Yii::app()->end();
		}
	}
	
	public function actionForceMarry() //soa client
	{
		$no_type = $_POST['no_type'];//id piutang premi
		
		$model=new TrxReceipt;
		$model->coa_debet_id = $no_type;
		$model->tgl_transaksi = date('Y-m-d');
		$model->nota_note = $_POST['nota_debet_no'];
		$model->coa_credit_id = 177; // id administrasi bank
		$model->jns_mata_uang = '1';
		$model->posting = '2';
		$model->no_receipt = 'FM-To Client';
		$model->nominal = $_POST['kurang_bayar'];
		if($model->save()){
			echo "1";
		}else{
			echo "0";
		}
		exit;
	}
	
	public function actionForceMarryinsurance() //soa insurance
	{
		$no_type = $_POST['no_type'];//id piutang premi
		if($no_type == "21"){ // piutang premi harta benda dst.
			$no_type == "94"; // hutang premi harta benda dst.
		}else if($no_type == "22"){
			$no_type == "95"; // hutang kendaraan bermotor
		}else if($no_type == "23"){
			$no_type == "96"; // hutang premi kendaraan bermotor
		}else if($no_type == "24"){
			$no_type == "97"; // HP kesehatan
		}else if($no_type == "25"){
			$no_type == "98"; // HP jiwa
		}else if($no_type == "26"){
			$no_type == "99";
		}else{
			$no_type == "0";
		}
		$model=new TrxReceipt;
		$model->coa_debet_id = 177;
		$model->tgl_transaksi = date('Y-m-d');
		$model->nota_note = $_POST['nota_debet_no'];
		$model->coa_credit_id = $no_type;
		$model->jns_mata_uang = '1';
		$model->posting = '2';
		$model->no_receipt = 'FM-To Insurance';
		$model->nominal = $_POST['kurang_bayar'];
		if($model->save()){
			echo "1";
		}else{
			echo "0";
		}
		exit;
	}
}